package com.pucong.gitee.database.backup.business.database.util;

import com.pucong.gitee.database.backup.business.database.mapper.AutoExeSqlLogMapper;
import com.pucong.gitee.database.backup.common.entity.database.AutoDataSource;
import com.pucong.gitee.database.backup.common.entity.database.vo.DatabaseTableFiledVo;
import com.pucong.gitee.database.backup.common.entity.database.vo.DatabaseTableVo;
import com.pucong.gitee.database.backup.common.entity.database.vo.DbTableFiledVo;
import com.pucong.gitee.database.backup.common.entity.database.vo.ExeSqlResult;
import com.pucong.gitee.database.backup.common.selfenum.CommonBoolenEnum;
import com.pucong.gitee.database.backup.common.selfenum.DatabaseDriverClassEnum;
import com.pucong.gitee.database.backup.configuration.exception.BusinessException;
import org.apache.commons.lang3.StringUtils;
import org.springframework.util.DigestUtils;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Optional;

/**
 * Oracle操作实现
 *
 * <p>支持情况：</p>
 * <p>1. SID方式 [支持]</p>
 * <p>2. TNSName方式 [不支持]</p>
 * <p>3. ServiceName方式 [不支持]</p>
 *
 * @author clouds3n
 * @since 2022-01-27
 */
public class OperationServiceOracleImpl extends AbstractOperationService {

    private static final String URL_PREFIX = "jdbc:oracle:thin:@";

    private static final String URL_LAST = "useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true";

    public OperationServiceOracleImpl(AutoDataSource autoDataSource, AutoExeSqlLogMapper autoExeSqlLogMapper) {
        super(autoDataSource, autoExeSqlLogMapper);
    }

    @Override
    public void initUrl() {
        this.url = URL_PREFIX + autoDataSource.getHost() + ":" + autoDataSource.getPort() + ":" + autoDataSource.getDatabaseName();
        this.driverClassName = DatabaseDriverClassEnum.oracle.getClassName();
    }

    @Override
    public String getGET_BASE_LIST_SQL() {
        return "select NAME from v$database";
    }

    @Override
    public String getGET_BASE_SCHEMA_LIST_SQL() {
        return "select username schemaName from DBA_USERS where account_status like '%OPEN%'";
    }

    @Override
    public String getGET_TABLE_LIST_SQL() {
        return "select t.table_name as tableName, t.num_rows as rowCounts, c.comments as tableComment\n" +
                "from user_tables t\n" +
                "join all_tab_comments c on t.table_name = c.table_name";
    }

    @Override
    public String getGET_TABLE_FILED_LIST_SQL() {
        return "select \n" +
                "t.table_name tableName, \n" +
                "t.column_id columnId, \n" +
                "t.column_name filedName,\n" +
                "t.data_type  filedType,\n" +
                "case when t.data_type = 'VARCHAR2' then t.data_length\n" +
                "else t.data_precision end filedSize,\n" +
                "t.data_scale filedSizeRadix,\n" +
                "t.data_default defaultValue,\n" +
                "case t.nullable when 'Y' then 1 when 'N' then 0 end nullable, \n" +
                "c.comments filedComment,\n" +
                "case when t.column_name in (SELECT column_name FROM user_cons_columns WHERE constraint_name = (\n" +
                "  SELECT constraint_name FROM user_constraints \n" +
                "  WHERE UPPER(table_name) = UPPER(?) AND CONSTRAINT_TYPE = 'P'\n" +
                ")) then 'PRI' else null end PRIMARYKEY\n" +
                "from user_tab_columns t\n" +
                "left join user_col_comments c on\n" +
                "t.column_name = c.column_name\n" +
                "and t.table_name = c.table_name\n" +
                "where t.table_name = ?\n" +
                "order by t.table_name , t.column_id";
    }

    @Override
    public List<String> getSchemaList(String databaseName) {
        List<String> resultStrList = new ArrayList<>();
        ExeSqlResult exeSqlResult = this.executeQuery(getGET_BASE_SCHEMA_LIST_SQL(), null);
        List<Map<String, Object>> resultList = exeSqlResult.getResultList();
        for (Map<String, Object> stringObjectMap : resultList) {
            resultStrList.add(stringObjectMap.get("SCHEMANAME").toString());
        }
        return resultStrList;
    }

    @Override
    public List<DatabaseTableVo> getTableListByDBInfo(String databaseName, String schemaName) {
        List<DatabaseTableVo> resultTableList = new ArrayList<>();
        ExeSqlResult exeSqlResult = this.executeQuery(getGET_TABLE_LIST_SQL(), null);
        List<Map<String, Object>> resultList = exeSqlResult.getResultList();
        for (Map<String, Object> stringObjectMap : resultList) {
            DatabaseTableVo databaseTableVo = new DatabaseTableVo();
            String tableName = Optional.ofNullable(stringObjectMap.get("TABLENAME")).map(String::valueOf).orElse(StringUtils.EMPTY);
            String count = Optional.ofNullable(stringObjectMap.get("ROWCOUNTS")).map(String::valueOf).orElse("0");
            String tableComment = Optional.ofNullable(stringObjectMap.get("TABLECOMMENT")).map(String::valueOf).orElse(StringUtils.EMPTY);
            databaseTableVo.setUuid(DigestUtils.md5DigestAsHex((autoDataSource.getUuid() + autoDataSource.getDatabaseName() + autoDataSource.getSchemaName() + tableName).getBytes()));
            databaseTableVo.setDataSize(new BigDecimal(count));
            databaseTableVo.setTableName(tableName);
            databaseTableVo.setTableComment(tableComment);
            resultTableList.add(databaseTableVo);
        }
        return resultTableList;
    }

    @Override
    public List<DbTableFiledVo> getTableFiledListByDBInfo(String databaseName, String schemaname) {
        return null;
    }

    @Override
    public List<DatabaseTableFiledVo> getTableFiledListByDBInfo(String databaseName, String schemaName, String tableName) {
        List<DatabaseTableFiledVo> resultTableList = new ArrayList<>();
        Object[] params = new Object[2];
        params[0] = tableName;
        params[1] = tableName;
        ExeSqlResult exeSqlResult = this.executeQuery(getGET_TABLE_FILED_LIST_SQL(), params);
        List<Map<String, Object>> resultList = exeSqlResult.getResultList();
        for (Map<String, Object> stringObjectMap : resultList) {
            DatabaseTableFiledVo databaseTableFiledVo = new DatabaseTableFiledVo();
            String filedName = getMapValue(stringObjectMap, "FILEDNAME");
            String filedSize = getMapValue(stringObjectMap, "FILEDSIZE");
            String filedType = getMapValue(stringObjectMap, "FILETYPE");
            String primaryKey = getMapValue(stringObjectMap, "PRIMARYKEY");
            String notNull = getMapValue(stringObjectMap, "NULLABLE");
            String defaultValue = getMapValue(stringObjectMap, "DEFAULTVALUE");
            String filedComment = getMapValue(stringObjectMap, "FILEDCOMMENT");
            databaseTableFiledVo.setFiledName(filedName);
            databaseTableFiledVo.setFiledSize(filedSize);
            databaseTableFiledVo.setFiledType(filedType);
            databaseTableFiledVo.setPrimaryKey("PRI".equals(primaryKey) ? CommonBoolenEnum.是 : CommonBoolenEnum.否);
            databaseTableFiledVo.setNotNull("YES".equals(notNull) ? CommonBoolenEnum.是 : CommonBoolenEnum.否);
            databaseTableFiledVo.setFiledComment(filedComment);
            databaseTableFiledVo.setDefaultValue(defaultValue);
            resultTableList.add(databaseTableFiledVo);
        }
        return resultTableList;
    }

    @Override
    public String generatePageSql(String sql, Integer pageNum, Integer pageSize) {
        if (StringUtils.isBlank(sql)) {
            throw new BusinessException("sql语句为空");
        }
        if (pageNum == null) {
            pageNum = 1;
        }
        if (pageSize == null) {
            pageSize = 10;
        }
        return "SELECT * FROM\n" +
                "(\n" +
                "    SELECT a.*, rownum r__\n" +
                "    FROM\n" +
                "    (\n" +
                sql +
                "    ) a\n" +
                "    WHERE rownum < ((pageNumber * pageSize) + 1 )\n" +
                ")\n" +
                "WHERE r__ >= (((pageNumber-1) * pageSize) + 1)";
    }
}
